package sealte.hvpms.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import sealte.hvpms.dao.RuleDAO;
import sealte.hvpms.pojo.RuleLocks;
import sealte.hvpms.util.DBConnection;

public class RuleDAOImpl implements RuleDAO{
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	
	public void addEmail(int poll_id,String email){
		String sql = "insert into hvpms_rule_email(poll_id,email) values(?,?)";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			ps.setString(2, email);
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
	}
	
	public void deleteAllEmailByPollId(int poll_id){
		String sql = "delete from hvpms_rule_email where poll_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
	}
	
	public List<RuleLocks> getAllEmail(int poll_id){
		List<RuleLocks> all = new ArrayList<RuleLocks>();
		String sql = "select email from hvpms_rule_email where poll_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			rs = ps.executeQuery();
			while(rs.next()){
				RuleLocks rl = new RuleLocks();
				rl.setPoll_id(poll_id);
				rl.setLocks(rs.getString("email"));
				all.add(rl);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return all;
	}
	
	public boolean isEmailExist(int poll_id,String email){
		boolean flag = false;
		String sql = "select * from hvpms_rule_email where poll_id=? and email=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			ps.setString(2, email);
			rs = ps.executeQuery();
			if(rs.next()){
				flag = true;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs,ps, conn);
		}
		return flag;
	}
	
	public void addIPAddr(int poll_id,String ipaddr){
		String sql = "insert into hvpms_rule_iplocks(poll_id,ip) values(?,?)";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			ps.setString(2, ipaddr);
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
	}
	
	public void deleteAllIPAddrByPollId(int poll_id){
		String sql = "delete from hvpms_rule_iplocks where poll_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
	}
	
	public boolean isIPAddrExist(int poll_id,String ipaddr){
		boolean flag = false;
		String sql = "select * from hvpms_rule_iplocks where poll_id=? and ip=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			ps.setString(2, ipaddr);
			rs = ps.executeQuery();
			if(rs.next()){
				flag = true;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs,ps, conn);
		}
		return flag;
	}
	
	public void addIDCard(int poll_id,String idcard){
		String sql = "insert into hvpms_rule_idcard(poll_id,idcard) values(?,?)";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			ps.setString(2, idcard);
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
	}
	
	public void deleteAllIDCardByPollId(int poll_id){
		String sql = "delete from hvpms_rule_idcard where poll_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
	}
	
	public List<RuleLocks> getAllIDCard(int poll_id){
		List<RuleLocks> all = new ArrayList<RuleLocks>();
		String sql = "select idcard from hvpms_rule_idcard where poll_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			rs = ps.executeQuery();
			while(rs.next()){
				RuleLocks rl = new RuleLocks();
				rl.setPoll_id(poll_id);
				rl.setLocks(rs.getString("idcard"));
				all.add(rl);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return all;
	}
	
	public boolean isIDCardExist(int poll_id,String idcard){
		boolean flag = false;
		String sql = "select * from hvpms_rule_idcard where poll_id=? and idcard=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			ps.setString(2, idcard);
			rs = ps.executeQuery();
			if(rs.next()){
				flag = true;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs,ps, conn);
		}
		return flag;
	}
}
